package com.bjsxt.shopping.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bjsxt.shopping.util.Cart;
import com.bjsxt.shopping.util.CartItem;
import com.bjsxt.shopping.util.DB;
import com.bjsxt.shopping.util.OrderDAO;
import com.bjsxt.shopping.util.Product;
import com.bjsxt.shopping.util.SalesItem;
import com.bjsxt.shopping.util.SalesOrder;
import com.bjsxt.shopping.util.User;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class OrderMysqlDAO implements OrderDAO {

	
	public boolean save(SalesOrder so) {
		Connection conn=null;
		PreparedStatement pstmt=null;
		String sql="";
		ResultSet rsKey=null;
		
		try {
			conn=(Connection)DB.getConn();
			if(so.getId()==-1){
			   sql="insert into salesorder values(null,?,?,?,?)";
			}
			conn.setAutoCommit(false);
			
			pstmt=DB.prepare(conn, sql,true);
			pstmt.setInt(1, so.getUser().getId());
			pstmt.setString(2, so.getAddr());
			pstmt.setTimestamp(3, so.getOdate());
			pstmt.setInt(4,so.getStatus());
			pstmt.executeUpdate();
			rsKey=pstmt.getGeneratedKeys();
			rsKey.next();
			int key=rsKey.getInt(1);
			
			String sqlItem="insert into salesitem values(null,?,?,?,?)";
			pstmt=DB.prepare(conn, sqlItem);
			Cart c=so.getCart();
			List<CartItem> citems=c.getItems();
			for(int i=0;i<citems.size();i++){
				CartItem ci=citems.get(i);
				
			pstmt.setInt(1, ci.getProductId());
			pstmt.setDouble(2, ci.getPrice());
			pstmt.setInt(3, ci.getCount());
			pstmt.setInt(4,key);
			pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
			
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.setAutoCommit(true);
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return false;
		}finally{
			DB.close(pstmt);
			DB.close(rsKey);
			DB.close(conn);
		}
		return true;
	}


	public int getOrders(List<SalesOrder> orders, int pageNo, int pageSize) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rsCount=null;
		int pageCount=0;
		ResultSet rs = null;
		try {
			conn = (Connection) DB.getConn();
			stmt = (Statement) DB.getStatement(conn);
			rsCount = (ResultSet) stmt.executeQuery("select count(*) from salesorder");
			rsCount.next();
			pageCount=(rsCount.getInt(1)+pageSize-1)/pageSize;
			rs = (ResultSet) stmt.
			executeQuery("select salesorder.id,salesorder.userid,salesorder.addr,salesorder.odate,salesorder.status," +
					"user.id uid,user.password,user.username,user.phone,user.addr uaddr, user.rdate from salesorder left join user " +
					" on (salesorder.userid = user.id) limit " + (pageNo-1)*pageSize +","+pageSize);
			
			while (rs.next()) {
				User u=new User();
				u.setId(rs.getInt("uid"));
				u.setPassword(rs.getString("password"));
				u.setUsername(rs.getString("username"));
				u.setAddr(rs.getString("uaddr"));
				u.setPhone(rs.getString("phone"));
				u.setRdate(rs.getTimestamp("rdate"));
				
				SalesOrder o =new SalesOrder();
				o.setId(rs.getInt("id"));
				o.setAddr(rs.getString("addr"));
				o.setOdate(rs.getTimestamp("odate"));
				o.setStatus(rs.getInt("status"));
				o.setUser(u);
				
				orders.add(o);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(rsCount);
			DB.close(stmt);
			DB.close(conn);
		}
		return pageCount;
	}

	
	public SalesOrder loadById(int id) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		SalesOrder o =null;
		try {
			conn = (Connection) DB.getConn();
			stmt = (Statement) DB.getStatement(conn);
			rs = (ResultSet) stmt.
			executeQuery("select salesorder.id,salesorder.userid,salesorder.addr,salesorder.odate,salesorder.status," +
					"user.id uid,user.password,user.username,user.phone,user.addr uaddr, user.rdate from salesorder left join user " +
					" on (salesorder.userid = user.id) where salesorder.id=" + id );
			
			while (rs.next()) {
				User u=new User();
				u.setId(rs.getInt("uid"));
				u.setPassword(rs.getString("password"));
				u.setUsername(rs.getString("username"));
				u.setAddr(rs.getString("uaddr"));
				u.setPhone(rs.getString("phone"));
				u.setRdate(rs.getTimestamp("rdate"));
				
				o=new SalesOrder();
				o.setId(rs.getInt("id"));
				o.setAddr(rs.getString("addr"));
				o.setOdate(rs.getTimestamp("odate"));
				o.setStatus(rs.getInt("status"));
				o.setUser(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return o;
	}


	public List<SalesItem> getSalesItem(SalesOrder so) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<SalesItem> si=null;
		try {
			conn = (Connection) DB.getConn();
			stmt = (Statement) DB.getStatement(conn);
			rs = (ResultSet) stmt.
			executeQuery("select salesorder.id,salesorder.userid,salesorder.addr,salesorder.odate,salesorder.status," +
					"product.id pid,product.name,product.descr,product.normalprice,product.memberprice, product.pdate,product.categoryid," +
					" salesitem.id sid,salesitem.productid,salesitem.orderid,salesitem.unitprice,salesitem.pcount " +
					" from salesorder join salesitem on (salesorder.id = salesitem.orderid) " +
					"join product on (salesitem.productid = product.id) " +
					" where salesorder.id= " + so.getId());
			
			while (rs.next()) {
				
				Product u=new Product();
				u.setId(rs.getInt("pid"));
				u.setName(rs.getString("name"));
				u.setDescr(rs.getString("descr"));
				u.setNormalPrice(rs.getDouble("normalprice"));
				u.setMemberPrice(rs.getDouble("memberprice"));
				u.setPdate(rs.getTimestamp("pdate"));
				
				SalesItem o =new SalesItem();
				o.setId(rs.getInt("sid"));
				o.setpCount(rs.getInt("pcount"));
				o.setUnitprice(rs.getDouble("unitprice"));
				
				o.setSalesorder(so);
				o.setProudct(u);
				si=new ArrayList<SalesItem>();
				si.add(o);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return si;
	}

	
	public boolean updateStatus(int id, int status) {
		Connection conn=null;
		PreparedStatement pstmt=null;
		String sql="update salesorder set status=? where id="+id;
		try {
			conn=(Connection)DB.getConn();
			pstmt=DB.prepare(conn, sql);
			pstmt.setInt(1, status);
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
